XplentyでRedshiftとBigQueryを往来してみる

はじめに

データアナリティクス事業本部のkobayashiです。

XplentyはETL、ELTツールとして様々なデータソースを扱え、また処理もXplentyのGUIで直感的に作成できます。 今回はXplentyを使ってRedshiftとBigQuery間でマイグレーションを行ってみたいと思います。

Xplenty | Simplified ETL & ELT to BigQuery, Snowflake, Redshift & Azure

接続設定の確認

XplentyによるRedshift,BigQueryへの接続方法はすでにエントリがありますのでそちらをご確認いただければ問題なく設定できます。

ただ問題なく設定できると書いたばかりですが、Xplentyのジョブを実行する途中で接続設定のミスでハマったしまったので注意点だけ記載しておきます。

移行元となる場合はRedshiftの場合はXplentyが所有するS3に、BigQueryの場合はGoogleCloudStorageにデータがUnloadされるようですが、データソースとストレージのリージョンがあっていないとジョブがエラー終了します。 接続設定ではRegionの値はデータソースのリージョンと一致していなくても接続テストするとSuccessとなるので注意が必要です。

移行用データの確認

移行前にそれぞれのデータソースは以下のようなデータとなっています。

• Redshift

select trim(s.nspname) as Schema,
       trim(t.name)      as Table,
       t.rows
from (
         select id, name, sum(rows) as rows
         from stv_tbl_perm
         group by id, name
     ) as t
         join pg_class as c on oid = t.id
         join pg_namespace as s on oid = c.relnamespace and s.nspname = 'cm_kobayashi_test'
order by t.name;

schema	table	rows
cm_kobayashi_test	date	365
cm_kobayashi_test	event	8798
cm_kobayashi_test	sales	172456
cm_kobayashi_test	venue	202

• BigQuery

select dataset_id
     , table_id
     , row_count
from ml_datasets.__TABLES__
order by table_id;

dataset_id	table_id	row_count
ml_datasets	census_adult_income	32561
ml_datasets	credit_card_default	2965
ml_datasets	iris	150
ml_datasets	ulb_fraud_detection	284807

BigQuery -> Redshiftへの移行

Xplentyでの設定は以下の順序で行います。

1. ソースとなるBigQueryのSourceコンポーネント作成
2. ターゲットとなるRedshiftのDestinationコンポーネント作成
3. 1,2を繰り返して移行対象のテーブルを登録
4. ジョブ実行

すべてGUIで設定できますので難なく設定できると思います。

はじめにサイドバーからPackagesを選択してNew Packageを押下し新しいパッケージを作成します。

BigQueryのSourceコンポーネント作成

XplentyのPackageでBigQueryコンポーネントを追加して移行したいデータテーブルを設定します。

手順1-1）.Add componetを押下し、SourcesでBigQueryを選択する。

手順1-2）.作成されたアイコンをダブルクリックするとBigQueryコンポーネント設定用のモーダルが表示されるので、設定済みのBigQueryのconnectionを選択する。

手順1-3）.Chose input connectionで対象の接続設定を選択する。

手順1-4）.Source propertiesでTableを選択し、テーブル名を入力しNextを押下する。

手順1-5）.読み込むカラムの選択とデータのプレビューが行なえBiqQueryのデータテーブルからデータが取れることが確認できるので、Select allを押下して移行対象のカラムを追加する。

手順1-6）.右下のSaveを押下して設定を保存する。

ここまででソースの設定は終わります。設定項目も少なくとても簡単です。

RedshiftのDestinationコンポーネント作成

次にRedshiftコンポーネントを追加して設定を行います。

手順2-1）.Add componetを押下し、SourcesでBigQueryを選択する。

手順2-2）.画面上にRedshiftのアイコンが作成されるので先に作成したBigQueryのアイコンとDrag&Dropで紐付ける。この紐付けをしていないと移行元であるBigQueryのカラムと移行先のRedshiftのカラムがマップできないのでRedshiftの設定を行う前に紐付けしておく。

手順2-3）.RedshiftのアイコンをダブルクリックするとRedshiftコンポーネント設定用のモーダルが表示されるので、設定済みのRedshiftのconnectionを選択する。

手順2-4）.移行先のRedshiftのスキーマとテーブルを入力し各種設定を行い、Nextを押下する。(今回の例ではわかりやすいようにbq_のプリフィックスを付けています。)

• Automatically create table if it doesn't exist : チェックする
• Automatically add missing columns : チェックする
• Operation type : Append (Insert only)を選択する

(他の設定は一旦デフォルトで問題ありませんがソースデータの形式によって変更してください。)

手順2-5）.移行元の移行先のカラムマッピングを行う。移行先のテーブルは新規作成なのでAuto fillを押下して自動設定を行う。（2でBigQueryとRedshiftの紐付けをしていないとここが設定できないので事前に紐付けすることが大切です。）

手順2-6）.右下のSaveを押下して設定を保存する。

ここまでの作業で1テーブル分の移行設定が終わりましたのでこれを繰り返してテーブル分設定します。 今回は4テーブルを移行するので最終的に以下のような形のパッケージになります。

ここまで設定が終わりましたら後はRun jobを押下することでジョブが走り設定に問題がなければマイグレーションが完了します。

移行した後のRedshiftのテーブルは情報は以下になり無事移行ができていることが確認できました。

schema	table	rows
cm_kobayashi_test	bq_census_adult_income	32561
cm_kobayashi_test	bq_credit_card_default	2965
cm_kobayashi_test	bq_iris	150
cm_kobayashi_test	bq_ulb_fraud_detection	284807
cm_kobayashi_test	date	365
cm_kobayashi_test	event	8798
cm_kobayashi_test	sales	172456
cm_kobayashi_test	venue	202

注意点

RedshiftのDestinationコンポーネント作成の設定(手順2-4)で「Automatically add missing columns : チェックする」を指定した場合はターゲットになるテーブルにカラムが存在しなければ自動で作成されますが、型は手順1-5で設定した型が選択されサイズは最大で作成されます。

例) ソースの型がSTRING -> 自動作成される型はvarchar(65535)

気になる場合は

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

で変更してください。

Redshift -> BigQuery への移行

設定の手順は「BigQuery -> Redshiftへの移行」と逆を行えば良いので、

1. ソースとなるRedshiftのSourceコンポーネント作成
2. ターゲットとなるBigQueryのDestinationコンポーネント作成
3. 1,2を繰り返して移行対象のテーブルを登録
4. ジョブ実行

で行います。途中の設定は「BigQuery -> Redshiftへの移行」を踏襲すれば特に困ることなく進められますので割愛します。

ジョブ完了後のBigQueryのテーブル情報を見ると正常に移行されていることが確認できます。

dataset_id	table_id	row_count
ml_datasets	census_adult_income	32561
ml_datasets	credit_card_default	2965
ml_datasets	iris	150
ml_datasets	rs_date	365
ml_datasets	rs_event	8798
ml_datasets	rs_sales	172456
ml_datasets	rs_venue	202
ml_datasets	ulb_fraud_detection	284807

まとめ

Xplentyを使うことでノーコーディングでRedshiftとBigQuery間でマイグレーションが行えました。またデータを加工したい場合もSource properties、Destination propertiesでおおよそ設定できるのでよほど込み入ったことを行わない限りXplentyでまかなえるのではないかと感じました。 データマイグレーションにお困りの方にはぜひお勧めします。

最後まで読んで頂いてありがとうございました。